﻿/*  
 *  IIS LogFile Analyser, Copyright (C) 2013. http://www.iis-analyser.com
 *  --
 *  This program is free software, provided "AS IS" WITHOUT ANY WARRANTY; without even the implied warranty of 
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
 * 
 *  File Author: Ian Quigley,    2013-02-23
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace DataStore
{
    [TableName("Visit")]
    public class VisitRecord
    {
        [FieldDescription("Id", "Long, primary key")]
        public long Id { get; private set; }
        [FieldDescription("FileId", "Long, foreign key to File table")]
        public long FileId { get; internal set; }
        [FieldDescription("SiteId", "Long, foreigh key to Site table")]
        public long SiteId { get; internal set; }

        [FieldDescription("Visit", "Datetime, of http request")]
        public DateTime Visit { get; set; }

        [FieldDescription("ClientIpId", "long, foreign key to IpAddress")]
        public IpAddressRecord ClientIP;
        [FieldDescription("ServerIpId", "long, foreign key to IpAddress")]
        public IpAddressRecord ServerIP;

        [FieldDescription("SiteNameId", "long, foreign key to SiteName")]
        public SiteNameRecord SiteName;
        [FieldDescription("ComputerNameId", "long, foreign key to ComputerName")]
        public ComputerNameRecord ComputerName;

        [FieldDescription("Port", "long, Port number")]
        public long Port { get; set; }

        [FieldDescription("MethodId", "long, foreign key to Method")]
        public MethodRecord Method;
        [FieldDescription("UriId", "long, foreign key to URI")]
        public URIRecord URI;
        [FieldDescription("QueryId", "long, foreign key to Query")]
        public QueryRecord Query;
        [FieldDescription("UsernameId", "long, foreign key to Username")]
        public UsernameRecord Username;

        [FieldDescription("Status", "Long, status")]
        public long Status { get; set; }
        [FieldDescription("SubStatus", "Long, substatus")]
        public long SubStatus { get; set; }
        [FieldDescription("Win32Status", "Long, win32-status")]
        public long Win32Status { get; set; }
        [FieldDescription("BytesSent", "Long")]
        public long BytesSent { get; set; }
        [FieldDescription("BytesRecieved", "Long")]
        public long BytesRecieved { get; set; }
        [FieldDescription("TimeTaken", "Long, milliseconds taken for request")]
        public long TimeTaken { get; set; }

        [FieldDescription("ProtocolVersionId", "long, foreign key to ProtocolVersion")]
        public ProtocolVersionRecord ProtocolVersion;
        [FieldDescription("HostId", "long, foreign key to Host")]
        public HostRecord Host;
        [FieldDescription("UserAgentId", "long, foreign key to UserAgent")]
        public UserAgentRecord UserAgent;
        [FieldDescription("CookieId", "long, foreign key to Cookie")]
        public CookieRecord Cookie;
        [FieldDescription("RefererId", "long, foreign key to Referer")]
        public RefererRecord Referer;

    
        internal VisitRecord()
        {
            SiteName = new SiteNameRecord(   );
            ComputerName = new ComputerNameRecord(   );
            Method = new MethodRecord(   );
            URI = new URIRecord(   );
            Query = new QueryRecord(   );
            Username = new UsernameRecord(   );
            ProtocolVersion = new ProtocolVersionRecord(   );
            Host = new HostRecord(   );
            UserAgent = new UserAgentRecord(   );
            Cookie = new CookieRecord(   );
            Referer = new RefererRecord(   );
            ClientIP = new IpAddressRecord(   );
            ServerIP = new IpAddressRecord(   );
        }

        internal static void InitialiseTables( )
        {
            using (var db = Database.Open())
            using (SQLiteCommand command = new SQLiteCommand( "CREATE TABLE if not exists Visit (" +
                "Id Integer PRIMARY KEY AutoIncrement, " +
                "FileId Integer, " +
                "SiteId Integer, " +
                "Visit DateTime, " +
                "ClientIpId long, " +
                "ServerIpId long, " +
                "SiteNameId long, " +
                "ComputerNameId long, " +                
                "Port long, " +
                "MethodId long, " + 
                "URIId long, " +
                "QueryId long, " +
                "UsernameId long, " +
                "Status long, " +
                "SubStatus long, " +
                "Win32Status long, " +
                "BytesSent long, " +
                "BytesRecieved long, " +
                "TimeTaken long, " +
                "ProtocolVersionId , "+
                "HostId long, " +
                "UserAgentId long, " +
                "CookieId long, " +
                "RefererId long " +
                ")", db.Connection ))
            {
                db.Connection.Open();
                command.ExecuteNonQuery();
            }

            (new SiteNameRecord(  )).InitialiseTable();
            (new ComputerNameRecord(  )).InitialiseTable();
            (new MethodRecord(  )).InitialiseTable();
            (new URIRecord(  )).InitialiseTable();
            (new QueryRecord(  )).InitialiseTable();
            (new UsernameRecord(  )).InitialiseTable();
            (new ProtocolVersionRecord(  )).InitialiseTable();
            (new HostRecord(  )).InitialiseTable();
            (new UserAgentRecord(  )).InitialiseTable();
            (new CookieRecord(  )).InitialiseTable();
            (new RefererRecord(  )).InitialiseTable();
            (new IpAddressRecord(  )).InitialiseTable();
        }

        public void Save()
        {
            using (var db = Database.Open())
            using (SQLiteCommand command = new SQLiteCommand("INSERT OR REPLACE INTO Visit (" +
                                (Id == 0 ? "" : "Id,") +
                                "FileId, SiteId, Visit, ClientIpId, ServerIpId, " +
                                "SiteNameId, ComputerNameId, Port, MethodId, URIId, QueryId, UsernameId, " +
                                "Status, SubStatus, Win32Status, BytesSent, BytesRecieved, " + 
                                "TimeTaken, ProtocolVersionId, HostId, UserAgentId, CookieId, RefererId) " + 
                                "VALUES (" + 
                                (Id == 0 ? "" : "@Id, ") +
                                "@FileId, @SiteId, @Visit, @ClientIpId, @ServerIpId, " +
                                "@SiteNameId, @ComputerNameId, @Port, @MethodId, @URIId, @QueryId, @UsernameId, " +
                                "@Status, @SubStatus, @Win32Status, @BytesSent, @BytesRecieved, " + 
                                "@TimeTaken, @ProtocolVersionId, @HostId, @UserAgentId, @CookieId, @RefererId); " + 
                                "select last_insert_rowid(); ", db.Connection))
                                {

                                    if (Id != 0)
                                        command.Parameters.AddWithValue( "@Id", Id );

                                    command.Parameters.AddWithValue( "@FileId", FileId );
                                    command.Parameters.AddWithValue( "@SiteId", SiteId );
                                    command.Parameters.AddWithValue( "@Visit", Visit );

                                    command.Parameters.AddWithValue( "@ServerIpId", ServerIP.Id );
                                    command.Parameters.AddWithValue( "@ClientIpId", ClientIP.Id );                                    

                                    command.Parameters.AddWithValue( "@SiteNameId", SiteName.Id);
                                    command.Parameters.AddWithValue( "@ComputerNameId", ComputerName.Id );
                                    
                                    command.Parameters.AddWithValue( "@Port", Port );
                                    command.Parameters.AddWithValue( "@MethodId", Method.Id );
                                    command.Parameters.AddWithValue( "@URIId", URI.Id );
                                    command.Parameters.AddWithValue( "@QueryId", Query.Id );
                                    command.Parameters.AddWithValue( "@UsernameId", Username.Id );

                                    command.Parameters.AddWithValue( "@Status", Status );
                                    command.Parameters.AddWithValue( "@SubStatus", SubStatus );
                                    command.Parameters.AddWithValue( "@Win32Status", Win32Status );
                                    command.Parameters.AddWithValue( "@BytesSent", BytesSent );
                                    command.Parameters.AddWithValue( "@BytesRecieved", BytesRecieved );
                                    command.Parameters.AddWithValue( "@TimeTaken", TimeTaken );
                                    command.Parameters.AddWithValue( "@ProtocolVersionId", ProtocolVersion.Id );
                                    command.Parameters.AddWithValue( "@HostId", Host.Id );
                                    command.Parameters.AddWithValue( "@UserAgentId", UserAgent.Id );
                                    command.Parameters.AddWithValue( "@CookieId", Cookie.Id );
                                    command.Parameters.AddWithValue( "@RefererId", Referer.Id );

                                    db.Connection.Open();
                                    var id = (long)command.ExecuteScalar();
                                    Id = id;
                }
        }
    }
}
